home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Visual Basic Source Code
/
Visual Basic Source Code.iso
/
vbsource
/
databa_1
/
database.bas
next >
Wrap
BASIC Source File
|
1999-07-10
|
15KB
|
387 lines
Attribute VB_Name = "DataBaseApp"
' DataBaseApp.bas
'
' By Herman Liu
'
' An extract from an actual application of mine (with modifications to reduce extra features,
' e.g. option to use query, allowance for change of fields, and search facilities).
' -----------------------------------------------------------------------------------------
' -----------------------------------------------------------------------------------------
' PURPOSES:
' (1) To show how to use the ADO Schema to obtain a list of tables of a database.
' (2) To show how to provide a re-usable single form (the same form can be used for any
' MDB file name, and another form can be used to display as many tables as there are
' in that database. Otherwise, you will need 10 forms if there are 10 tables).
' (3) To show the possible techniques to enable opening several tables on the screen in
' the same session (using the same form) yet without conflict.
' (4) To show how to display various attributes/properties in descriptive text which is
' more understandable, rather than the VB's original numeric codes.
'
' REMARKS:
' MDI form
' "Window" menu is provided so that you can switch between tables opened, if you
' open more than one on the screen.
' TABLES form
' (1) Double click a field name will display field properties (alternatively highlight
' that field name and click "Field Property" button).
' (2) Double click a table name will invokd the GRID form (alternatively highlight
' that table name and click "Table" button).
' GRID form
' (1) Click the tiny colored buttons below the Grid will show various attributes/
' properties.
' -----------------------------------------------------------------------------------------
'
Option Explicit
Public gFileSpec As String ' Filespec of MDB
Public gTableName As String ' Table name of selected MDB
Public gstrFields() As String
Public gstrFieldsOrig() As String
Public gfso As FileSystemObject
Public gcdg As Object
Public gAcnn As adodb.Connection
Public gstrCNN As String
' Exclude fields for null terminated string and fields for pictures
Public Const gconexcludeFieldTypes = " 8/128/204/205"
Sub Main()
Set gfso = New FileSystemObject
Set gcdg = frmFrame.CommonDialog1
gFileSpec = ""
gTableName = ""
frmFrame.Show
End Sub
Sub DBFilesMDBproc()
On Error GoTo errhandler
' Obtain gFileSpec
Dim i As Integer
If GetFileSpec("(*.mdb)|*.mdb") = True Then
If UCase(Right(gFileSpec, 4)) <> ".MDB" Then
MsgBox "Please select a .MDB file"
Exit Sub
End If
Set gAcnn = New adodb.Connection
gAcnn.CursorLocation = adUseClient
gstrCNN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & gFileSpec & ";"
' Only gAcnn, not gRcnn
If (gAcnn.Errors.Count > 0) Then
' Just Display The First Error In The Collection
MsgBox "Error: " & gAcnn.Errors(0).Description, _
0, "Connect Error!"
Exit Sub
End If
frmTablesTVW.Show
End If
Exit Sub
' Provided a way to exit, if error occurred in called form
' forcing it to be closed
errhandler:
ErrMsgProc "basMain DBFilesMDBProc"
End Sub
Function GetFileSpec(ByVal strFilter As String) As Boolean
On Error GoTo errhandler
Dim tmpfile As String
tmpfile = gFileSpec
Do
frmFrame.CommonDialog1.CancelError = True
frmFrame.CommonDialog1.FileName = tmpfile
frmFrame.CommonDialog1.Filter = strFilter
frmFrame.CommonDialog1.ShowOpen
If frmFrame.CommonDialog1.FileName = "" Then
Exit Do
End If
tmpfile = frmFrame.CommonDialog1.FileName
If gfso.FileExists(tmpfile) = True Then
Exit Do
End If
MsgBox "File specification not found. Please re-try"
Loop
If tmpfile <> "" Then
gFileSpec = tmpfile
GetFileSpec = True
Else
GetFileSpec = False
End If
Exit Function
errhandler:
GetFileSpec = False
If Err.Number <> 32755 Then
ErrMsgProc "basMain GetFileSpec"
End If
End Function
Sub ErrMsgProc(mMsg As String)
MsgBox mMsg & vbCrLf & Err.Number & Space(5) & Err.Description
End Sub
' Convert the numeric value returned by DB to Enum, so
' that at least the user could have a guess of what it is.
Function ConvType(ByVal TypeVal As Long) As String
Select Case TypeVal
Case adBigInt ' 20
ConvType = "adBigInt"
Case adBinary ' 128
ConvType = "adBinary"
Case adBoolean ' 11
ConvType = "adBoolean"
Case adBSTR ' 8 i.e. null terminated string
ConvType = "adBSTR"
Case adChar ' 129
ConvType = "adChar"
Case adCurrency ' 6
ConvType = "adCurrency"
Case adDate ' 7
ConvType = "adDate"
Case adDBDate ' 133
ConvType = "adDBDate"
Case adDBTime ' 134
ConvType = "adDBTime"
Case adDBTimeStamp ' 135
ConvType = "adDBTimeStamp"
Case adDecimal ' 14
ConvType = "adDecimal"
Case adDouble ' 5
ConvType = "adDouble"
Case adEmpty ' 0
ConvType = "adEmpty"
Case adError ' 10
ConvType = "adError"
Case adGUID ' 72
ConvType = "adGUID"
Case adIDispatch ' 9
ConvType = "adIDispatch"
Case adInteger ' 3
ConvType = "adInteger"
Case adIUnknown ' 13
ConvType = "adIUnknown"
Case adLongVarBinary ' 205
ConvType = "adLongVarBinary"
Case adLongVarChar ' 201
ConvType = "adLongVarChar"
Case adLongVarWChar ' 203
ConvType = "adLongVarWChar"
Case adNumeric ' 131
ConvType = "adNumeric"
Case adSingle ' 4
ConvType = "adSingle"
Case adSmallInt ' 2
ConvType = "adSmallInt"
Case adTinyInt ' 16
ConvType = "adTinyInt"
Case adUnsignedBigInt ' 21
ConvType = "adUnsignedBigInt"
Case adUnsignedInt ' 19
ConvType = "adUnsignedInt"
Case adUnsignedSmallInt ' 18
ConvType = "adUnsignedSmallInt"
Case adUnsignedTinyInt ' 17
ConvType = "adUnsignedTinyInt"
Case adUserDefined ' 132
ConvType = "adUserDefined"
Case adVarBinary ' 204
ConvType = "adVarBinary"
Case adVarChar ' 200
ConvType = "adVarChar"
Case adVariant ' 12
ConvType = "adVariant"
Case adVarWChar ' 202
ConvType = "adVarWChar"
Case adWChar ' 130
ConvType = "adWChar"
End Select
End Function
Function ConvAttr(ByVal mAttr As Long) As String
' Note value of mAttr is often a combination of several values
' hence chances are "Unknown" in the following
Select Case mAttr
Case (mAttr And adFldMayDefer)
ConvAttr = "adFldMayDefer " '2
Case (mAttr And adFldUpdatable)
ConvAttr = "adFldUpdatable " '4
Case (mAttr And adFldUnknownUpdatable)
ConvAttr = "adFldUnknownUpdatable " '8
Case (mAttr And adFldFixed)
ConvAttr = "adFldFixed " '16
Case (mAttr A